package com.joinway.framework.site.service;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import javax.sql.DataSource;

import org.apache.commons.collections4.CollectionUtils;
import org.apache.commons.lang3.StringUtils;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.jdbc.datasource.DataSourceUtils;

import com.joinway.framework.extension.utils.NumberUtils;
import com.joinway.framework.site.beans.view.OrderCondition;
import com.joinway.framework.site.beans.view.PageView;
import com.joinway.framework.site.beans.view.QueryCondition;
import com.joinway.framework.site.beans.view.QueryForm;
import com.joinway.framework.site.constants.QueryConstants;

public class PageService {

	private final static Logger log = LoggerFactory.getLogger(PageService.class);
	
	private Connection conn;

	private int defaultRows = 10;
	
	public PageService(DataSource dataSource){
		conn = DataSourceUtils.getConnection(dataSource);
	}
	
	public PageView findRecord(QueryForm form) throws SQLException{
		PageView view = new PageView();
		
		int page = NumberUtils.convertToInt(form.getPage());
		page = page <= 0 ? 1 : page;
		
		view.setCurrentPage(page);
		
		int rows = NumberUtils.convertToInt(form.getRows());
		rows = rows == 0 ? defaultRows : rows;
		
		String table = StringUtils.trimToEmpty(form.getTarget());
		StringBuilder sql = new StringBuilder("select * from ").append(table).append(" where 1 = 1 ");
		
		String whereClause = buildWhereClause(form.getConditions());
		sql.append(whereClause).append(" ").append(buildOrderClause(form.getOrderConditions()));
		
		StringBuilder countSql = new StringBuilder();
		countSql.append("select count(*) from (")
			.append(sql.toString())
			.append(") t");
		PreparedStatement stmt = conn.prepareStatement(countSql.toString());
		ResultSet resultSet = stmt.executeQuery();
		if(resultSet.next()){
			int totalPage = resultSet.getInt(1);
			view.setTotalPage(totalPage);
		}
		
		StringBuilder pageSql = new StringBuilder();
		pageSql.append(sql.toString())
			.append(" limit ")
			.append((page - 1) * rows).append(", ").append(rows);
		
		stmt = conn.prepareStatement(pageSql.toString());
		resultSet = stmt.executeQuery();
		int columnCount = resultSet.getMetaData().getColumnCount();
		List<Map<String, ?>> records = new ArrayList<>();
		
		while(resultSet.next()){
			Map<String, Object> row = new HashMap<>();
			for(int i = 1; i <= columnCount; i++){
				String columnName = resultSet.getMetaData().getColumnName(i);
				Object value = resultSet.getObject(i);
				row.put(columnName, value);
			}
			
			records.add(row);
		}
		
		view.setRows(records);
		
		return view;
	}
	
	protected String buildWhereClause(List<QueryCondition> conditions){
		StringBuilder clause = new StringBuilder();
		
		if(CollectionUtils.isEmpty(conditions)){
			return clause.toString();
		}
		
		for(QueryCondition qc : conditions){
			clause.append(QueryConstants.Condition.And)
				.append(" ")
				.append(buildWhereClauseItem(qc))
				.append(" ");
		}
		
		return clause.toString();
	}
	
	protected String buildWhereClauseItem(QueryCondition qc){
		StringBuilder item = new StringBuilder();
		
		String field = qc.getField();
		String operator = StringUtils.isBlank(qc.getOperator()) ? QueryConstants.Operator.Like : qc.getOperator();
		String type = StringUtils.isBlank(qc.getType()) ? QueryConstants.FieldType.Text : qc.getType();
		String fromValue = StringUtils.trimToNull(qc.getFromValue());
		String toValue = StringUtils.trimToNull(qc.getToValue()); 
		
		do{
			if(operator == QueryConstants.Operator.Like){
				item.append(buildQueryField(field, type)).append(" ");
				if(fromValue == null){
					item.append("is null");
				}else{
					item.append(operator)
						.append(" '%").append(fromValue).append("%'");
				}
				break;
			}
			
			if(operator == QueryConstants.Operator.Between){
				if(fromValue != null && toValue != null){
					item.append(buildQueryField(field, type))
						.append(" ")
						.append(operator)
						.append(" ")
						.append(buildQueryValue(fromValue, type))
						.append(", ")
						.append(buildQueryValue(toValue, type));
					break;
				}else if(fromValue == null && toValue != null){
					operator = QueryConstants.Operator.LessOrEqualsTo;
				}else if(fromValue != null && toValue == null){
					operator = QueryConstants.Operator.GreaterOrEqualsTo;
				}else{
					break;
				}
				fromValue = toValue;
			}
			
			if(operator == QueryConstants.Operator.Equals
					|| operator == QueryConstants.Operator.LessOrEqualsTo 
					|| operator == QueryConstants.Operator.Less
					|| operator == QueryConstants.Operator.GreaterOrEqualsTo
					|| operator == QueryConstants.Operator.Greater){
				item.append(buildQueryField(field, type)).append(" ");
				if(fromValue == null){
					item.append("is null");
				}else{
					item.append(operator)
						.append(" ")
						.append(buildQueryValue(fromValue, type));
				}
				break;
			}
		}while(false);
		
		return item.toString();
	}
	
	protected String buildQueryField(String field, String type){
		StringBuilder target = new StringBuilder();
		
		do{
			if(type == QueryConstants.FieldType.Date){
				target.append("date_format(").append(field).append(",'").append("%Y%m%d").append("')");
				break;
			}
			
			if(type == QueryConstants.FieldType.DateTime){
				target.append("date_format(").append(field).append(",'").append("%Y%m%d%k%i%s").append("')");
				break;
			}
			
			target.append(field);
		}while(false);
		
		return target.toString();
	}
	
	protected String buildQueryValue(String value, String type){
		StringBuilder target = new StringBuilder();
		
		do{
			if(type == QueryConstants.FieldType.Text){
				target.append("'").append(value).append("'");
				break;
			}
			
			target.append(value);
		}while(false);
		
		return target.toString();
	}

	protected String buildOrderClause(List<OrderCondition> conditions){
		StringBuilder clause = new StringBuilder();
		
		if(CollectionUtils.isEmpty(conditions)){
			return clause.toString();
		}
		
		for(OrderCondition oc : conditions){
			String order = StringUtils.isBlank(oc.getOrder()) ? QueryConstants.Order.Asc : oc.getOrder();
			clause.append(", ").append(oc.getField()).append(" ").append(order);
		}
		
		return StringUtils.replaceOnce(clause.toString(), ",", "order by");
	}
	
	public int getDefaultRows() {
		return defaultRows;
	}

	public void setDefaultRows(int defaultRows) {
		this.defaultRows = defaultRows;
	}
}

